const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库初始化脚本
const initDatabase = async () => {
  let connection;
  
  try {
    // 连接到MySQL服务器（不指定数据库）
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      charset: 'utf8mb4'
    });

    console.log('🔗 连接到MySQL服务器成功');

    // 创建数据库
    const dbName = process.env.DB_NAME || 'music_player';
    await connection.execute(`CREATE DATABASE IF NOT EXISTS \`${dbName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    console.log(`✅ 数据库 ${dbName} 创建成功`);

    // 使用数据库
    await connection.execute(`USE \`${dbName}\``);

    // 创建用户表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        nickname VARCHAR(100),
        avatar VARCHAR(255),
        phone VARCHAR(20),
        gender TINYINT DEFAULT 0 COMMENT '0:未知 1:男 2:女',
        birthday DATE,
        signature TEXT,
        vip_level TINYINT DEFAULT 0 COMMENT 'VIP等级',
        vip_expire_time DATETIME,
        status TINYINT DEFAULT 1 COMMENT '1:正常 0:禁用',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 用户表创建成功');

    // 创建歌手表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS artists (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        alias VARCHAR(255),
        avatar VARCHAR(255),
        description TEXT,
        country VARCHAR(50),
        birth_date DATE,
        gender TINYINT DEFAULT 0,
        status TINYINT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 歌手表创建成功');

    // 创建专辑表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS albums (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(200) NOT NULL,
        artist_id INT,
        cover_url VARCHAR(255),
        description TEXT,
        release_date DATE,
        company VARCHAR(100),
        status TINYINT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE SET NULL,
        INDEX idx_artist (artist_id),
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 专辑表创建成功');

    // 创建歌曲表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS songs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(200) NOT NULL,
        artist_id INT,
        album_id INT,
        duration INT DEFAULT 0 COMMENT '时长(秒)',
        file_url VARCHAR(255),
        cover_url VARCHAR(255),
        lyrics TEXT,
        play_count INT DEFAULT 0,
        like_count INT DEFAULT 0,
        quality TINYINT DEFAULT 1 COMMENT '1:标准 2:高品质 3:无损',
        file_size INT DEFAULT 0,
        bitrate INT DEFAULT 0,
        status TINYINT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE SET NULL,
        FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE SET NULL,
        INDEX idx_artist (artist_id),
        INDEX idx_album (album_id),
        INDEX idx_name (name),
        INDEX idx_play_count (play_count)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 歌曲表创建成功');

    // 创建歌单表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS playlists (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(200) NOT NULL,
        description TEXT,
        cover_url VARCHAR(255),
        user_id INT,
        is_public TINYINT DEFAULT 1 COMMENT '1:公开 0:私有',
        play_count INT DEFAULT 0,
        like_count INT DEFAULT 0,
        song_count INT DEFAULT 0,
        tags VARCHAR(255),
        status TINYINT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user (user_id),
        INDEX idx_public (is_public),
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 歌单表创建成功');

    // 创建歌单歌曲关联表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS playlist_songs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        playlist_id INT NOT NULL,
        song_id INT NOT NULL,
        sort_order INT DEFAULT 0,
        added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
        UNIQUE KEY uk_playlist_song (playlist_id, song_id),
        INDEX idx_playlist (playlist_id),
        INDEX idx_song (song_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 歌单歌曲关联表创建成功');

    // 创建用户收藏表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS user_favorites (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        song_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
        UNIQUE KEY uk_user_song (user_id, song_id),
        INDEX idx_user (user_id),
        INDEX idx_song (song_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 用户收藏表创建成功');

    // 创建播放历史表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS play_history (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        song_id INT NOT NULL,
        play_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        play_duration INT DEFAULT 0 COMMENT '播放时长(秒)',
        device_type VARCHAR(50),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
        INDEX idx_user_time (user_id, play_time),
        INDEX idx_song (song_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 播放历史表创建成功');

    // 创建用户歌单收藏表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS user_playlist_likes (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        playlist_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
        UNIQUE KEY uk_user_playlist (user_id, playlist_id),
        INDEX idx_user (user_id),
        INDEX idx_playlist (playlist_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log('✅ 用户歌单收藏表创建成功');

    console.log('🎉 数据库初始化完成！');

  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
};

// 运行初始化
if (require.main === module) {
  initDatabase();
}

module.exports = initDatabase;